﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class ThietBiDienDAO
    {
        public static DataTable DanhSach(string sql)
        {
            SqlConnection cn = DataProvider.Connection();
            SqlDataAdapter da = new SqlDataAdapter(sql, cn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        //code cho phần tư vấn------------
        public static DataTable Retrieve_Pin(float CongSuat, int LoaiPin)
        {
            DataTable dt = new DataTable();
            SqlConnection con = DataProvider.Connection();
            string sql = "usp_RetrievePin";

            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType  = CommandType.StoredProcedure;
            cmd.Parameters.Add("@CongSuat", SqlDbType.Float).Value = CongSuat;
            cmd.Parameters.Add("@LoaiPin", SqlDbType.Int).Value = LoaiPin;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            return dt;
        }

        public static DataTable Retrive_Acquy(int MaLoaiAcquy)
        {
            DataTable dt = new DataTable();
                
            SqlConnection con = DataProvider.Connection();
            string sql = "usp_RetriveAcquy";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType  = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Loai", SqlDbType.Float).Value = MaLoaiAcquy;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            return dt;
        }

        //lấy acquy đc chọn ra để tính toán số lượng, thành tiền
        public static DataTable Retrieve_ThietBi(int MaThietBi)
        {
            DataTable dt = new DataTable();

            SqlConnection con = DataProvider.Connection();
            string sql = "usp_Retrive_ThietBi";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@MaThietBi", SqlDbType.Float).Value = MaThietBi;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            return dt;
        }

        //lấy bộ sạc phù hợp V theo loại và A theo pin
        public static DataTable Retrieve_BoSac(int loai)
        {
            //usp_Retrieve_BoSac
            DataTable dt = new DataTable();
            SqlConnection con = DataProvider.Connection();
            string sql = "usp_Retrieve_BoSac";

            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Loai", SqlDbType.Int).Value = loai;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            return dt;
        }

        //lấy bộ kích điện có W phù hợp
        public static DataTable Retrieve_BoKichDien(int loai, float congsuat)
        {
            //usp_Retrieve_Inverter
            DataTable dt = new DataTable();
            SqlConnection con = DataProvider.Connection();
            string sql = "usp_Retrieve_Inverter";

            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Loai", SqlDbType.Int).Value = loai;
            cmd.Parameters.Add("@W", SqlDbType.Float).Value = congsuat;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            return dt;
        }
        //--------------------------------

        public static int CapNhatTBPIN(ThietBiDienDTO _tbd)
        {
            SqlConnection conn = DataProvider.Connection();
            conn.Open();
            string str = "usp_CapNhatTBPIN";
            SqlCommand cmd = new SqlCommand(str, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@TenTb", SqlDbType.NVarChar).Value = _tbd.TenThietBi;
            cmd.Parameters.Add("@LoaiThietBi", SqlDbType.NVarChar).Value = _tbd.LoaiThietBi;
            cmd.Parameters.Add("@DonGia", SqlDbType.Float).Value = _tbd.DonGia;
            cmd.Parameters.Add("@CongXuat", SqlDbType.Float).Value = _tbd.CongSuat;
            cmd.Parameters.Add("@KichThuoc", SqlDbType.VarChar).Value = _tbd.KickThuoc;
            cmd.Parameters.Add("@Vmp", SqlDbType.Float).Value = _tbd.Vmp;
            cmd.Parameters.Add("@Imp", SqlDbType.Float).Value = _tbd.Imp;
            cmd.Parameters.Add("@Isc", SqlDbType.Float).Value = _tbd.Isc;
            cmd.Parameters.Add("@Voc", SqlDbType.Float).Value = _tbd.Voc;
            cmd.Parameters.Add("@Ma", SqlDbType.Int).Value = _tbd.MaLoaiThietBi;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }

        public static int CapNhatTBACQUY(ThietBiDienDTO _tbd)
        {
            SqlConnection conn = DataProvider.Connection();
            conn.Open();
            string str = "usp_CapNhatTBACQUY";
            SqlCommand cmd = new SqlCommand(str, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@TenTb", SqlDbType.NVarChar).Value = _tbd.TenThietBi;
            cmd.Parameters.Add("@LoaiThietBi", SqlDbType.NVarChar).Value = _tbd.LoaiThietBi;
            cmd.Parameters.Add("@DonGia", SqlDbType.Float).Value = _tbd.DonGia;
            cmd.Parameters.Add("@CongXuat", SqlDbType.Float).Value = _tbd.CongSuat;
            cmd.Parameters.Add("@KichThuoc", SqlDbType.VarChar).Value = _tbd.KickThuoc;
            cmd.Parameters.Add("@Vmp", SqlDbType.Float).Value = _tbd.Vmp;
            cmd.Parameters.Add("@Imp", SqlDbType.Float).Value = _tbd.Imp;
            //cmd.Parameters.Add("@Isc", SqlDbType.Float).Value = _tbd.Isc;
            //cmd.Parameters.Add("@Voc", SqlDbType.Float).Value = _tbd.Voc;
            cmd.Parameters.Add("@Ma", SqlDbType.Int).Value = _tbd.MaLoaiThietBi;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }

        public static int CapNhatTBBOKICHDIEN(ThietBiDienDTO _tbd)
        {
            SqlConnection conn = DataProvider.Connection();
            conn.Open();
            string str = "usp_CapNhatTBBOKICHDIEN";
            SqlCommand cmd = new SqlCommand(str, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@TenTb", SqlDbType.NVarChar).Value = _tbd.TenThietBi;
            cmd.Parameters.Add("@LoaiThietBi", SqlDbType.NVarChar).Value = _tbd.LoaiThietBi;
            cmd.Parameters.Add("@DonGia", SqlDbType.Float).Value = _tbd.DonGia;
            cmd.Parameters.Add("@CongXuat", SqlDbType.Float).Value = _tbd.CongSuat;            
            cmd.Parameters.Add("@Ma", SqlDbType.Int).Value = _tbd.MaLoaiThietBi;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }

        public static int CapNhatTBBOSAC(ThietBiDienDTO _tbd)
        {
            SqlConnection conn = DataProvider.Connection();
            conn.Open();
            string str = "usp_CapNhatTBBOSAC";
            SqlCommand cmd = new SqlCommand(str, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@TenTb", SqlDbType.NVarChar).Value = _tbd.TenThietBi;
            cmd.Parameters.Add("@LoaiThietBi", SqlDbType.NVarChar).Value = _tbd.LoaiThietBi;
            cmd.Parameters.Add("@DonGia", SqlDbType.Float).Value = _tbd.DonGia;
            cmd.Parameters.Add("@CongXuat", SqlDbType.Float).Value = _tbd.CongSuat;
            cmd.Parameters.Add("@Ma", SqlDbType.Int).Value = _tbd.MaLoaiThietBi;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }

        public static DataTable LayDanhSachPIN()
        {
            SqlConnection conn = DataProvider.Connection();
            conn.Open();
            string sql = "usp_LayDanhSachPIN";
            SqlDataAdapter da = new SqlDataAdapter(sql,conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable LayDanhSachACQUY()
        {
            SqlConnection conn = DataProvider.Connection();
            conn.Open();
            string sql = "usp_LayDanhSachACQUY";
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable LayDanhSachBOKICHDIEN()
        {
            SqlConnection conn = DataProvider.Connection();
            conn.Open();
            string sql = "usp_LayDanhSachBOKICHDIEN";
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable LayDanhSachSAC()
        {
            SqlConnection conn = DataProvider.Connection();
            conn.Open();
            string sql = "usp_LayDanhSachBOSAC";
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            conn.Close();
            return dt;
        }

        public static int ThemThietBiPIN(ThietBiDienDTO _tbd)
        {
            SqlConnection conn = DataProvider.Connection();
            conn.Open();
            string sql = "usp_ThemTBDTPIN";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@TenTb", SqlDbType.NVarChar).Value = _tbd.TenThietBi;
            cmd.Parameters.Add("@LoaiThietBi", SqlDbType.NVarChar).Value = _tbd.LoaiThietBi;
            cmd.Parameters.Add("@DonGia", SqlDbType.Float).Value = _tbd.DonGia;
            cmd.Parameters.Add("@CongXuat", SqlDbType.Float).Value = _tbd.CongSuat;
            cmd.Parameters.Add("@KichThuoc", SqlDbType.VarChar).Value = _tbd.KickThuoc;            
            cmd.Parameters.Add("@Vmp", SqlDbType.Float).Value = _tbd.Vmp;
            cmd.Parameters.Add("@Imp", SqlDbType.Float).Value = _tbd.Imp;
            cmd.Parameters.Add("@Isc", SqlDbType.Float).Value = _tbd.Isc;
            cmd.Parameters.Add("@Voc", SqlDbType.Float).Value = _tbd.Voc;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }

        public static int ThemThietBiACQUY(ThietBiDienDTO _tbd)
        {
            SqlConnection conn = DataProvider.Connection();
            conn.Open();
            string sql = "usp_ThemTBDTACQUY";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@TenTb", SqlDbType.NVarChar).Value = _tbd.TenThietBi;
            cmd.Parameters.Add("@LoaiThietBi", SqlDbType.NVarChar).Value = _tbd.LoaiThietBi;
            cmd.Parameters.Add("@DonGia", SqlDbType.Float).Value = _tbd.DonGia;
            cmd.Parameters.Add("@CongXuat", SqlDbType.Float).Value = _tbd.CongSuat;
            cmd.Parameters.Add("@KichThuoc", SqlDbType.VarChar).Value = _tbd.KickThuoc;
            cmd.Parameters.Add("@Vmp", SqlDbType.Float).Value = _tbd.Vmp;
            cmd.Parameters.Add("@Imp", SqlDbType.Float).Value = _tbd.Imp;
            //cmd.Parameters.Add("@Isc", SqlDbType.Float).Value = _tbd.Isc;
            //cmd.Parameters.Add("@Voc", SqlDbType.Float).Value = _tbd.Voc;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }

        public static int ThemThietBiBOKICHDIEN(ThietBiDienDTO _tbd)
        {
            SqlConnection conn = DataProvider.Connection();
            conn.Open();
            string sql = "usp_ThemTBDTBOKICHDIEN";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@TenTb", SqlDbType.NVarChar).Value = _tbd.TenThietBi;
            cmd.Parameters.Add("@LoaiThietBi", SqlDbType.NVarChar).Value = _tbd.LoaiThietBi;
            cmd.Parameters.Add("@DonGia", SqlDbType.Float).Value = _tbd.DonGia;
            cmd.Parameters.Add("@CongXuat", SqlDbType.Float).Value = _tbd.CongSuat;
            //cmd.Parameters.Add("@KichThuoc", SqlDbType.VarChar).Value = _tbd.KickThuoc;
            //cmd.Parameters.Add("@Vmp", SqlDbType.Float).Value = _tbd.Vmp;
            //cmd.Parameters.Add("@Imp", SqlDbType.Float).Value = _tbd.Imp;
            //cmd.Parameters.Add("@Isc", SqlDbType.Float).Value = _tbd.Isc;
            //cmd.Parameters.Add("@Voc", SqlDbType.Float).Value = _tbd.Voc;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }

        public static int ThemThietBiBOSAC(ThietBiDienDTO _tbd)
        {
            SqlConnection conn = DataProvider.Connection();
            conn.Open();
            string sql = "usp_ThemTBDTBOSAC";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@TenTb", SqlDbType.NVarChar).Value = _tbd.TenThietBi;
            cmd.Parameters.Add("@LoaiThietBi", SqlDbType.NVarChar).Value = _tbd.LoaiThietBi;
            cmd.Parameters.Add("@DonGia", SqlDbType.Float).Value = _tbd.DonGia;
            cmd.Parameters.Add("@CongXuat", SqlDbType.Float).Value = _tbd.CongSuat;
            //cmd.Parameters.Add("@KichThuoc", SqlDbType.VarChar).Value = _tbd.KickThuoc;
            //cmd.Parameters.Add("@Vmp", SqlDbType.Float).Value = _tbd.Vmp;
            //cmd.Parameters.Add("@Imp", SqlDbType.Float).Value = _tbd.Imp;
            //cmd.Parameters.Add("@Isc", SqlDbType.Float).Value = _tbd.Isc;
            //cmd.Parameters.Add("@Voc", SqlDbType.Float).Value = _tbd.Voc;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }

        public static int KiemTraTBDT(string tbtd)
        {

            SqlConnection conn = DataProvider.Connection();
            conn.Open();
            string sql = "usp_KiemTraTBDT";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@TenTb", SqlDbType.NVarChar).Value = tbtd;
            cmd.Parameters.Add("@k", SqlDbType.Int);
            cmd.Parameters["@k"].Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            int k = Int32.Parse(cmd.Parameters["@k"].Value.ToString());
            if (k == 1)
                return 1;
            else
                return 0;
        }

        public static DataTable TraCuuThietBiDien(ThietBiDienDTO _tbd, int _MaThietBi)
        {
            SqlConnection conn = DataProvider.Connection();
            conn.Open();
            string sql = "usp_TraCuuThietBiDien";
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@MaTb", SqlDbType.Int).Value = _MaThietBi;
            cmd.Parameters.Add("@TenTb", SqlDbType.NVarChar).Value = _tbd.TenThietBi;
            cmd.Parameters.Add("@LoaiThietBi", SqlDbType.Int).Value = _tbd.MaLoaiThietBi;
            cmd.Parameters.Add("@DonGia", SqlDbType.Float).Value = _tbd.DonGia;
            cmd.Parameters.Add("@CongXuat", SqlDbType.Float).Value = _tbd.CongSuat;
            cmd.Parameters.Add("@KichThuoc", SqlDbType.VarChar).Value = _tbd.KickThuoc;
            cmd.Parameters.Add("@Vmp", SqlDbType.Float).Value = _tbd.Vmp;
            cmd.Parameters.Add("@Imp", SqlDbType.Float).Value = _tbd.Imp;
            cmd.Parameters.Add("@Isc", SqlDbType.Float).Value = _tbd.Isc;
            cmd.Parameters.Add("@Voc", SqlDbType.Float).Value = _tbd.Voc;

            //da.SelectCommand = cmd;
            DataTable kq = new DataTable();
            kq.Load(cmd.ExecuteReader());
            //da.Fill(kq);
            conn.Close();
            return kq;
        }
    }

    
}
